package com.ruge.util.syncdata;

import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.*;
import org.junit.jupiter.params.ParameterizedTest;
import org.junit.jupiter.params.provider.ValueSource;

import java.sql.*;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.List;

@Slf4j
@TestInstance(TestInstance.Lifecycle.PER_CLASS) // 单例测试实例，共享连接
public class V_CS_BG_XSBB_JYBS_BMCP_2023 {


    private Connection report; // 数据库连接（共享）
    private Connection sapHana; // 数据库连接（共享）


    // 1. 初始化：在所有测试方法前执行（获取连接）
    @BeforeAll
    void initAll() throws Exception {
        // 从工具类获取Oracle连接（实际项目中建议用配置文件）
        report = DbUtil.getOracleReportConn();
        // 关闭自动提交，便于事务控制
        report.setAutoCommit(false);
        log.info("报表数据库连接:{}", report);
        sapHana = DbUtil.getSapHanaConn();
        sapHana.setAutoCommit(false);
        log.info("sapHana数据库连接:{}", sapHana);
    }


    // 2. 每个测试方法执行前的准备工作（如清理测试数据）
    @BeforeEach
    void init() throws Exception {
        // 测试前清空测试表（避免干扰）
//        String cleanSql = "DELETE FROM test_user WHERE user_id > 1000";
//        try (PreparedStatement ps = report.prepareStatement(cleanSql)) {
//            ps.executeUpdate();
//        }
    }

    // 3. 测试查询操作（带显示名称）
    @Test
    void test() throws SQLException {
// 定义起始日期和结束日期
        LocalDate startDate = LocalDate.of(2023, 1, 1);
        LocalDate endDate = LocalDate.of(2023, 1, 2); // 修改为2023-12-31
//        LocalDate endDate = LocalDate.of(2023, 12, 31); // 修改为2023-12-31

        // 定义日期格式化器
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd");

        int totalCount = DbUtil.getTotalCount(sapHana, "SELECT count(1) FROM V_DIM_FD_X");
        int fdPage = (int) Math.ceil((double) totalCount / DbUtil.DEFAULT_PAGE_SIZE);
        List<JSONObject> fdList = DbUtil.queryListSapHana(sapHana, "SELECT * FROM V_DIM_FD_X ORDER BY FDBH", 1);
        // 遍历并打印所有日期
        LocalDate currentDate = startDate;
        while (!currentDate.isAfter(endDate)) {
            String date = currentDate.format(formatter);
            log.info("SELECT count(1) FROM V_DIM_FD_X 共计:{} 条数据,共计{}页", totalCount, fdPage);
            String V_CS_XSBB_JYBS_BMCP_2023 = buildV_CS_XSBB_JYBS_BMCP_2023(date);
            String V_BG_XSBB_JYBS_BMCP_2023 = buildV_BG_XSBB_JYBS_BMCP_2023(date);
            DbUtil.execUpdate(sapHana, "drop view V_CS_XSBB_JYBS_BMCP_2023");
            sapHana.commit();
            DbUtil.execUpdate(sapHana, "drop view V_BG_XSBB_JYBS_BMCP_2023");
            sapHana.commit();
            DbUtil.execUpdate(sapHana, V_CS_XSBB_JYBS_BMCP_2023);
            sapHana.commit();
            DbUtil.execUpdate(sapHana, V_BG_XSBB_JYBS_BMCP_2023);
            sapHana.commit();
            for (JSONObject entries : fdList) {
                String fdbh = entries.getString("FDBH");
                String sql = "SELECT count(1)  FROM V_CS_BG_XSBB_JYBS_BMCP_2023 where fdbh = " + fdbh;
                int fdTotal = DbUtil.getTotalCount(sapHana, sql);
                List<JSONObject> jsonObjects = DbUtil.queryListSapHana(sapHana, "select RQ, FDBH, FLAG, SPFL, ZCJY, THJY from V_CS_BG_XSBB_JYBS_BMCP_2023 where fdbh = " + fdbh, 1);
               // highPerformanceBatchInsert(report, jsonObjects, DbUtil.DEFAULT_PAGE_SIZE);
                log.info("插入完成~日期:{} 分店:{} count:{} 查询的数量:{}", date, fdbh, fdTotal, jsonObjects.size());
            }

            // 日期+1
            currentDate = currentDate.plusDays(1);
        }
    }


    public void highPerformanceBatchInsert(Connection conn, List<JSONObject> jsonList, int batchSize) {
        String sql = "INSERT INTO M_XSBB_FD_SPFL_JYBS_000001 (RQ, FDBH, FLAG, SPFL, ZCJY, THJY) VALUES (?, ?, ?, ?, ?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            conn.setAutoCommit(false);
            int count = 0;
            for (JSONObject json : jsonList) {
                pstmt.setDate(1, new java.sql.Date(json.getDate("RQ").getTime()));
                pstmt.setString(2, json.getString("FDBH"));
                pstmt.setString(3, json.getString("FLAG"));
                pstmt.setString(4, json.getString("SPFL"));
                pstmt.setInt(5, json.getInteger("ZCJY"));
                pstmt.setInt(6, json.getInteger("THJY"));
                pstmt.addBatch();
                count++;
                // 分批提交
                if (count % batchSize == 0) {
                    pstmt.executeBatch();
                    conn.commit();
                    log.info("已提交~日期:{} 分店:{} count:{} 条记录", json.getDate("RQ"), json.getString("FDBH"), count);
                }
            }

            // 提交剩余记录
            pstmt.executeBatch();
            conn.commit();
            System.out.println("批量插入完成，总共 " + count + " 条记录");
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("批量插入失败", e);
        }
    }


    public String buildV_CS_XSBB_JYBS_BMCP_2023(String date) {
        return "create view V_CS_XSBB_JYBS_BMCP_2023 AS " +
                "SELECT A.JZRQ               RQ --小程序门店经营进度报表用   交易笔数\n" +
                "          ,A.SKTNO              SKTNO\n" +
                "          ,A.JLBH    \t\t\tJLBH\n" +
                "          ,C.FDBH\t\t\t\tFDBH\n" +
                "          ,C.NAME               FDMC\n" +
                "          ,E.SPFL               SCBH\n" +
                "          ,E.NAME               SCMC\n" +
                "          ,C.FDLXDM\t\t\t\tFDLXDM\n" +
                "          ,A.VIPID\t\t\t\tVIPID\n" +
                "          ,max(case when A.VIPID =-1 then 1 else 0 end) as JYBS_FHY\n" +
                "          ,sum(case when A.VIPID =-1 then B.XSJE else 0 end)  as XSJE_FHY\n" +
                "          ,sum(case when A.VIPID =-1 then B.YHJE else 0 end)  as YHJE_FHY\n" +
                "          ,max(case when A.VIPID > 0 then 1 else 0 end) as JYBS_HY\n" +
                "          ,sum(case when A.VIPID > 0 then B.XSJE else 0 end)  as   XSJE_HY\n" +
                "          ,sum(case when A.VIPID > 0 then B.YHJE else 0 end)  as YHJE_HY\n" +
                "          ,max(case when A.XSJE > 0 then 1 else 0 end) as zcjy --正常交易\n" +
                "          ,max(case when A.XSJE < 0 then 1 else 0 end) as thjy --退货交易\n" +
                "    FROM   CS_LSJL_SKTXSJL      A\n" +
                "          ,CS_LSJL_SKTXSJLC     B\n" +
                "          ,V_DIM_FD             C\n" +
                "          ,DIM_CS_SPXX          D\n" +
                "          ,DIM_CS_SPFL          E\n" +
                "    WHERE  A.SKTNO = B.SKTNO\n" +
                "           AND A.JLBH = B.JLBH\n" +
                "           AND '999' || B.FDBH = C.FDBH\n" +
                "           AND B.SP_ID = D.SP_ID\n" +
                "           AND D.SPFL = E.SPFL\n" +
                "           and A.JZRQ=b.JZRQ\n" +
                "\t\t   AND A.JZRQ >='20230101' --or a.JZRQ ='20220409' or  a.JZRQ ='20220408')--AND A.JZRQ <'20220101'\n" +
                "\t\t   AND A.JZRQ <'20240101' --or a.JZRQ ='20220409' or  a.JZRQ ='20220408')--AND A.JZRQ <'20220101'\n" +
                "    GROUP BY\n" +
                "           A.JZRQ\n" +
                "          ,A.SKTNO\n" +
                "          ,A.JLBH\n" +
                "          ,C.FDBH\n" +
                "          ,C.NAME\n" +
                "          ,E.SPFL\n" +
                "          ,E.NAME\n" +
                "          ,C.FDLXDM\n" +
                "          ,A.VIPID";
    }


    private String buildV_BG_XSBB_JYBS_BMCP_2023(String date) {
        return "create view V_BG_XSBB_JYBS_BMCP_2023 AS " +
                "SELECT A.JZRQ            RQ  --小程序门店经营进度报表用   交易笔数\n" +
                "\t      ,A.SKTNO\n" +
                "\t      ,A.JLBH\n" +
                "\t      ,C.FDBH\n" +
                "\t      ,C.NAME            FDMC\n" +
                "\t      ,D.bmid          SCBH\n" +
                "\t      ,D.BMMC       SCMC\n" +
                "\t      ,C.FDLXDM\n" +
                "\t      ,A.VIPID\t\t\t\tVIPID\n" +
                "\t      ,max(case when A.VIPID =-1 then 1 else 0 end) as JYBS_FHY\n" +
                "\t      ,sum(case when A.VIPID =-1 then B.XSJE else 0 end)  as XSJE_FHY\n" +
                "\t      ,sum(case when A.VIPID =-1 then B.YHJE else 0 end)  as YHJE_FHY\n" +
                "\t      ,max(case when A.VIPID > 0 then 1 else 0 end) as JYBS_HY\n" +
                "\t      ,sum(case when A.VIPID > 0 then B.XSJE else 0 end)  as   XSJE_HY\n" +
                "\t      ,sum(case when A.VIPID > 0 then B.YHJE else 0 end)  as YHJE_HY\n" +
                "\t      ,max(case when A.XSJE > 0 then 1 else 0 end) as zcjy --正常交易\n" +
                "\t      ,max(case when A.XSJE < 0 then 1 else 0 end) as thjy --退货交易\n" +
                "\tFROM   LSJL_SKTXSJL      A\n" +
                "\t      ,LSJL_SKTXSJLC     B\n" +
                "\t      ,V_DIM_FD          C\n" +
                "\t      ,V_DIM_FD_SC_BM   D\n" +
                "\tWHERE  A.DEPTID = B.DEPTID\n" +
                "\t       AND A.SKTNO = B.SKTNO\n" +
                "\t       AND A.JLBH = B.JLBH\n" +
                "\t       AND A.DEPTID = C.DEPTID\n" +
                "\t       AND SUBSTR(B.DEPTID_1, 1, 8) = d.bmid\n" +
                "\t       --and c.fdbh =2\n" +
//                "\t       AND A.JZRQ ='" + date + "' --or A.JZRQ ='20220409' or  A.JZRQ ='20220408') --AND A.JZRQ <'20220101'\n" +

                "\t\t   AND A.JZRQ >='20230101' --or a.JZRQ ='20220409' or  a.JZRQ ='20220408')--AND A.JZRQ <'20220101'\n" +
                "\t\t   AND A.JZRQ <'20240101' --or a.JZRQ ='20220409' or  a.JZRQ ='20220408')--AND A.JZRQ <'20220101'\n" +

                "\tGROUP BY\n" +
                "\t       A.JZRQ\n" +
                "\t      ,A.SKTNO\n" +
                "\t      ,A.JLBH\n" +
                "\t      ,C.FDBH\n" +
                "\t      ,C.NAME\n" +
                "\t      ,D.bmid\n" +
                "\t      ,D.BMMC\n" +
                "\t      ,C.FDLXDM\n" +
                "\t      ,A.VIPID";
    }

    // 4. 测试插入操作（参数化测试：多组数据）
    @ParameterizedTest
    @ValueSource(ints = {1002, 1003, 1004}) // 测试参数：用户ID
    @DisplayName("参数化测试：批量插入用户")
    void testInsertUser(int userId) throws SQLException {

    }

    // 5. 测试异常场景（如插入重复ID）
    @Test
    @DisplayName("测试插入重复ID时的异常")
    void testInsertDuplicateId() throws SQLException {

    }

    // 6. 每个测试方法执行后：回滚事务（避免测试数据污染数据库）
    @AfterEach
    void tearDown() throws SQLException {
        report.commit();
        sapHana.commit();
    }

    // 7. 所有测试结束后：关闭连接
    @AfterAll
    void tearDownAll() throws SQLException {
        if (report != null && !report.isClosed()) {
            report.close();
        }
        if (sapHana != null && !sapHana.isClosed()) {
            sapHana.close();
        }
    }
}
